﻿Public Class clsPWck
    Public Function getPwCkbyTv(oper_type As String) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.*,b.pw_date,b.into_date,b.assist_marker from sf.b_pw_ck a left join sf.b_pw_ck_in_details b "
        szSql &= " on a.oper_type = b.oper_type and a.type_value = b.type_value and a.batch_no=b.batch_no"
        szSql &= " where a.oper_type= ? and a.cur_num > 0 "
        szSql &= " group by a.sys_id,a.batch_no,a.oper_type,a.type_value,a.material_code,a.cur_num,a.update_time,b.pw_date,b.into_date,b.assist_marker"
        szSql &= " order by b.pw_date desc, b.into_date desc"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, oper_type)
        If IsNothing(dt) Then
            getPwCkbyTv = New DataTable
        Else
            getPwCkbyTv = dt
        End If
    End Function

    Public Function getPwOutbyTv(oper_type As String) As DataTable
        Dim szSql As String = ""
        szSql &= "select * from sf.b_pw_ck_out_details "
        szSql &= " where oper_type= ? and use_num = 0 "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, oper_type)
        If IsNothing(dt) Then
            getPwOutbyTv = New DataTable
        Else
            getPwOutbyTv = dt
        End If
    End Function

    Public Function insertCk(code As String, en As entitypw_ck, oper_u As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= "INSERT INTO sf.b_pw_ck_out_details(ck_out_code, batch_no, oper_type, type_value, material_code, out_num, pt_pgd_code, location, update_time,validity) "
            sql &= "VALUES ( ?, ?, ?, ?,?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)),?)"
            With en
                rtn = DBacs.ExecuteUpdateSql(sql, -1, code, .batch_no, .oper_type, .type_value, .material_code, .out_num, .pt_pgd_code, .location, .validity)
            End With
            sql = ""
            sql &= " select * from sf.b_pw_ck where batch_no = ? and oper_type = ? and type_value = ? and material_code = ?"
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, en.batch_no, en.oper_type, en.type_value, en.material_code)
            sql = ""
            If dt.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_pw_ck  "
                sql &= " SET cur_num = cur_num - ?, update_time=(SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE batch_no = ? and oper_type = ? and type_value = ? and material_code = ? "
                DBacs.ExecuteUpdateSql(sql, -1, en.out_num, en.batch_no, en.oper_type, en.type_value, en.material_code)
            End If
            sql = ""
            If en.oper_type = "1" Then
                sql &= "UPDATE sf.t_mst_flb "
            Else
                sql &= "UPDATE sf.t_mst_gjb "
            End If
            sql &= " SET cur_num = cur_num - ?, update_time=? "
            sql &= " WHERE mst_value = ? and material_code = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.out_num, Now(), en.type_value, en.material_code)

            sql = ""
            sql &= " select sys_id from sf.b_pw_ck_out_details order by update_time desc limit 1 "
            Dim dtout As DataTable = DBacs.ExecuteSql(sql, -1)

            Dim oper_num As Integer = en.out_num.ToString()
            sql = ""
            sql &= "INSERT INTO sf.b_gt(oper_type, type_value,material_code, batch_no,picking_num,status,operator,update_time,assist_marker,b_pw_ck_out_details_sys_id,validity) "
            sql &= "VALUES (?, ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)), ?, ?, ?)"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.oper_type, en.type_value, en.material_code, en.batch_no, oper_num, "0", oper_u, en.assist_marker, dtout.Rows(0)("sys_id"), "1")
            sql = ""
            sql &= " select * from sf.b_gt where oper_type = ? and type_value = ? and material_code = ? and status = ? and validity = ? order by update_time desc limit 1 "
            Dim dtgt As DataTable = DBacs.ExecuteSql(sql, -1, en.oper_type, en.type_value, en.material_code, "0", "1")
            sql = ""
            sql &= "INSERT INTO sf.b_gt_result_details(procedure_no,ok_num,b_gt_sys_id, update_time) "
            sql &= "VALUES (?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "00", oper_num, dtgt.Rows(0)("sys_id"))
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function insertCk(code As String, oper_u As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        Try
            sql &= "INSERT INTO sf.b_pw_ck_out( factory_id, ck_out_code, operator, update_time) "
            sql &= "VALUES ( ?, ?, ?, ?)"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "TS", code, oper_u, Now())
        Catch ex As Exception

        End Try
        Return rtn
    End Function

End Class
